package com.qiyu.service;

import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.lang.UUID;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.qiyu.db.StudentDBUtil;
import com.qiyu.entity.Student;

import java.sql.SQLException;
import java.util.List;

import static com.qiyu.db.StudentDBUtil.insertStudent;

/**
 * @Author： gebilaozhongyi
 * @Company：
 * @CreateTime： 2024-06-28 20:52
 * @Description：
 */
public class StudentServiceUtil {

    public static void addStudent(Student student) {
        if (student == null) {
            student.setNum(UUID.fastUUID().toString(true));
            student.setName("张三");
            student.setSex("男");
            student.setMath(100);
            student.setEnglish(100);
            student.setChina(100);
        }
        insertStudent(student);
    }

    public static void initStudents() {
        //https://www.hutool.cn/docs/#/core/%E6%96%87%E6%9C%AC%E6%93%8D%E4%BD%9C/CSV%E6%96%87%E4%BB%B6%E5%A4%84%E7%90%86%E5%B7%A5%E5%85%B7-CsvUtil
        final CsvReader reader = CsvUtil.getReader();
        List<Student> result = reader.read(ResourceUtil.getUtf8Reader("chengji.csv"), Student.class);
        result.forEach(StudentDBUtil::insertStudent);

    }

    public static String queryLowScoreCount() {
        try {
            return Db.use().queryString("select count(*) from student where math < 60 or english < 60 or china < 60");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static Entity queryAvgScore() {
        try {
            return Db.use().queryOne("select avg(math) as math,avg(english) as english,avg(china) as china from student");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static List<Entity> selectStudent(String s1) {
        try {
            return Db.use().query("select * from student where num like ?", "%" + s1 + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
